﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TestTracking.Common;

namespace TestTracking.Helper
{
    public class SqlHelp
    {
        SqlConnection sqlConn;
        SqlCommand sqlComm;
        SqlDataAdapter sqlDA;
        private readonly string connString;
        public SqlHelp()
        {
            connString = string.Format("server={0};database={1};User Id={2};Password={3}",Global.Server,Global.Database,Global.UserId,Global.Password);// "server=192.168.135.252;database=HIW;User Id=sa;Password=kerrlog@123";
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        void Open()
        {
            try
            {
                sqlConn = new SqlConnection(connString);
                sqlConn.Open();
            }
            catch
            {
                this.Close();//异常关闭数据库连接
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        void Close()
        {
            sqlConn?.Close();
        }
        /// <summary>
        /// 执行存储过程并返回数据表
        /// </summary>
        /// <param name="proceName">存储过程名称</param>
        /// <param name="sqlParameters">存储过程参数集</param>
        /// <param name="dt">存储过程返回的数据表</param>
        /// <returns></returns>
        public string ExecuteStoredProcedureAndReturnDatatable(string proceName, SqlParameter[] sqlParameters, out DataTable dt)
        {
            string retMsg = string.Empty;//数据库返回的状态码和状态信息
            DataSet ds = new DataSet();
            try
            {
                Open();
                using (sqlComm = new SqlCommand())
                {
                    sqlComm.Connection = sqlConn;
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    sqlComm.CommandText = proceName;
                    sqlComm.Parameters.AddRange(sqlParameters);
                    using (sqlDA = new SqlDataAdapter(sqlComm))
                    {
                        sqlDA.Fill(ds);
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[ds.Tables.Count - 1];
                        }
                        else
                        {
                            dt = new DataTable();
                        }

                        retMsg = sqlComm.Parameters["@StatusCode"].Value.ToString() + "|" + sqlComm.Parameters["@StatusMsg"].Value.ToString();// OK|操作成功！
                    }
                    //sqlComm.ExecuteNonQuery();

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return retMsg;
        }
    }
}
